import pathlib
import os
import sqlite3
from openpyxl import load_workbook  #openpyxl
import configparser
import sys

root=pathlib.Path(__file__).parent
conf = configparser.ConfigParser()
conf.read(str(root/"config.ini"), encoding="utf-8")
try:
    lock=conf.get("Lock","lock")
except:
    lock=False
finally:
    if(lock=="lock"):
        a=input("数据已被锁定")
        sys.exit(0)

file=root/pathlib.Path("学生信息.xlsx")
db=sqlite3.connect(str(root/"yj.db"))
cur = db.cursor()
njn={'初中一年级':7,'初中二年级':8,'初中三年级':9}

sql="SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
r=cur.execute(sql)
#print(r.fetchall())
if(len(r.fetchall())==0):
    sql=r'DROP TABLE IF EXISTS "main"; '
    cur.execute(sql)
    sql=r'CREATE TABLE "main" ("ID" text NOT NULL, "kh" TEXT, "name" TEXT, "bj" TEXT, "nj" TEXT, "kc" TEXT, "z" integer, "p" integer, PRIMARY KEY ("ID") );'
    cur.execute(sql)
    sql=r'CREATE UNIQUE INDEX "kh" ON "main" ("kh" ASC );'
    cur.execute(sql)
    sql=r'CREATE  VIEW re AS SELECT	* FROM	main a JOIN ( SELECT kc, z, p FROM main GROUP BY kc, z, p HAVING count( * ) > 1 ) b ON a.kc = b.kc 	AND a.z = b.z 	AND a.p = b.p ORDER BY a.kc,a.z,a.p'
    cur.execute(sql)
    '''
    sql=r'CREATE UNIQUE INDEX "main"."unique" ON "main" (   "kc",   "z",   "p" );' 
    cur.execute(sql)     
    '''
    db.commit()
else:
    cur.execute("delete from main")
    db.commit()

print(str(file))
wb = load_workbook(str(file))
wb_active=wb.active
wb_row=wb_active.rows
print(wb_active.max_row)
cur.execute("delete from main")
for i in range(2,wb_active.max_row+1):
    print(wb_active.cell(row=i,column=6).value,wb_active.cell(row=i,column=9).value)

    if(wb_active.cell(row=i,column=9).value=="正式生"):
        sql=r'INSERT INTO main(ID, kh, name, bj,nj, kc, z, p) VALUES ("{id}", "{kh}","{name}", "{bj}",{nj} ,0,0,0)'.format(
                    id=wb_active.cell(row=i,column=5).value,
                    kh=wb_active.cell(row=i,column=14).value,
                    name=wb_active.cell(row=i,column=6).value,
                    bj=str(njn[wb_active.cell(row=i,column=3).value])+"_"+str(wb_active.cell(row=i,column=4).value),
                    nj=njn[wb_active.cell(row=i,column=3).value]

                )
        print(sql)
        cur.execute(sql)
    else:
        print(wb_active.cell(row=i,column=6).value,wb_active.cell(row=i,column=5).value)

db.commit()
a=input("导入完成,按Enter键退出")
